﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using HRM.DAL.Gateway;

namespace HRM.ReportGateway
{
    public class ReportDataGateway:DBGateway
    {
        public DataTable GetPayReportByProjectAndMonth(int projId, string month, string year)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"SELECT tblCode.CodeName,tblEmployees.Name,tblDesignations.Title, tblProjects.ProjectName,tblPaySlip.Allowance,tblPaySlip.Incentive, 
                                    tblPaySlip.Deduction, tblPaySlip.PF, tblPaySlip.Netpay, tblPaySlip.Month, tblPaySlip.Year,tblCompany.name AS company,tblCompany.address
                                    FROM tblCode INNER JOIN
                                    tblEmployees ON tblCode.Id = tblEmployees.Code_CodeID INNER JOIN
                                    tblDesignations ON tblEmployees.DesignationID = tblDesignations.Id INNER JOIN
                                    tblProjects ON tblEmployees.ProjectID = tblProjects.ProjectID INNER JOIN
                                    tblPaySlip ON tblEmployees.EmployeeId = tblPaySlip.EmpId,dbo.tblCompany
                                    where(tblProjects.ProjectID='" + projId + "' and tblPaySlip.Month='" + month + "' and tblPaySlip.Year='" + year + "')";
                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(CommandObj);
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured during Retriving Data");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public DataTable GetPayReportByProjectAndYear(int projId, string year)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"SELECT tblCode.CodeName,tblEmployees.Name,tblDesignations.Title, tblProjects.ProjectName,tblPaySlip.Allowance,tblPaySlip.Incentive, 
                                    tblPaySlip.Deduction, tblPaySlip.PF, tblPaySlip.Netpay, tblPaySlip.Month, tblPaySlip.Year,tblCompany.name AS company,tblCompany.address
                                    FROM tblCode INNER JOIN
                                    tblEmployees ON tblCode.Id = tblEmployees.Code_CodeID INNER JOIN
                                    tblDesignations ON tblEmployees.DesignationID = tblDesignations.Id INNER JOIN
                                    tblProjects ON tblEmployees.ProjectID = tblProjects.ProjectID INNER JOIN
                                    tblPaySlip ON tblEmployees.EmployeeId = tblPaySlip.EmpId,dbo.tblCompany
                                    where(tblProjects.ProjectID='" + projId + "'and tblPaySlip.Year='" + year + "')";
                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(CommandObj);
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured during Retriving Data");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public DataTable GetPayReportByProject(int projId)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"SELECT tblCode.CodeName,tblEmployees.Name,tblDesignations.Title, tblProjects.ProjectName,tblPaySlip.Allowance,tblPaySlip.Incentive, 
                                    tblPaySlip.Deduction, tblPaySlip.PF, tblPaySlip.Netpay, tblPaySlip.Month, tblPaySlip.Year,tblCompany.name AS company,tblCompany.address
                                    FROM tblCode INNER JOIN
                                    tblEmployees ON tblCode.Id = tblEmployees.Code_CodeID INNER JOIN
                                    tblDesignations ON tblEmployees.DesignationID = tblDesignations.Id INNER JOIN
                                    tblProjects ON tblEmployees.ProjectID = tblProjects.ProjectID INNER JOIN
                                    tblPaySlip ON tblEmployees.EmployeeId = tblPaySlip.EmpId,dbo.tblCompany
                                    where(tblProjects.ProjectID='" + projId + "')";
                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(CommandObj);
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured during Retriving Data");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public DataTable GetYearlyExpenduture(string year)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"SELECT tblCode.CodeName,tblEmployees.Name,tblDesignations.Title, tblProjects.ProjectName,tblPaySlip.Allowance,tblPaySlip.Incentive, 
                                    tblPaySlip.Deduction, tblPaySlip.PF, tblPaySlip.Netpay, tblPaySlip.Month, tblPaySlip.Year,tblCompany.name AS company,tblCompany.address
                                    FROM tblCode INNER JOIN
                                    tblEmployees ON tblCode.Id = tblEmployees.Code_CodeID INNER JOIN
                                    tblDesignations ON tblEmployees.DesignationID = tblDesignations.Id INNER JOIN
                                    tblProjects ON tblEmployees.ProjectID = tblProjects.ProjectID INNER JOIN
                                    tblPaySlip ON tblEmployees.EmployeeId = tblPaySlip.EmpId,
                                    dbo.tblCompany
                                    where(tblPaySlip.Year='" + year + "')";
                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(CommandObj);
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured during Retriving Data");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public DataTable GetAllPayrollOfTheCompany()
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"SELECT tblCode.CodeName,tblEmployees.Name,tblDesignations.Title, tblProjects.ProjectName,tblPaySlip.Allowance,tblPaySlip.Incentive, 
                                    tblPaySlip.Deduction, tblPaySlip.PF, tblPaySlip.Netpay, tblPaySlip.Month, tblPaySlip.Year,tblCompany.name AS company,tblCompany.address
                                    FROM tblCode INNER JOIN
                                    tblEmployees ON tblCode.Id = tblEmployees.Code_CodeID INNER JOIN
                                    tblDesignations ON tblEmployees.DesignationID = tblDesignations.Id INNER JOIN
                                    tblProjects ON tblEmployees.ProjectID = tblProjects.ProjectID INNER JOIN
                                    tblPaySlip ON tblEmployees.EmployeeId = tblPaySlip.EmpId,
                                    dbo.tblCompany";
                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(CommandObj);
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured during Retriving Data");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }
    }
}